package org.spring.bse.home.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.spring.bse.home.model.Stock;

public class DBUtil
{
	public static Connection getConnection()
	{
		Connection con = null;
		try
		{
			Class.forName("org.sqlite.JDBC");
			con = DriverManager.getConnection("jdbc:sqlite:FinancePortfolio.db");
		}
		catch (Exception e)
		{
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		return con;
	}

	public static List<Object[]> getResultSet(String qryString)
	{
		List<Object[]> objList = null;
		ResultSet rs = null;
		Statement stmt = null;
		Connection con = null;
		try
		{
			con = getConnection();
			con.setAutoCommit(false);
			stmt = con.createStatement();
			rs = stmt.executeQuery(qryString);
			objList = getResultList(rs);
			rs.close();
			stmt.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return objList;
	}

	public static List<Object> getResultSet(String qryString, Class cls)
	{
		List<Object> objList = null;
		ResultSet rs = null;
		Statement stmt = null;
		Connection con = null;
		try
		{
			con = getConnection();
			con.setAutoCommit(false);
			stmt = con.createStatement();
			rs = stmt.executeQuery(qryString);
			objList = getResultList(rs, cls);
			// objList = getResultList(rs);
			rs.close();
			stmt.close();
			con.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return objList;
	}

	public static List<Object[]> getResultSet(Connection con, String qryString)
	{
		List<Object[]> objList = null;
		ResultSet rs = null;
		Statement stmt = null;
		try
		{
			con.setAutoCommit(false);
			stmt = con.createStatement();
			rs = stmt.executeQuery(qryString);
			objList = getResultList(rs);
			rs.close();
			stmt.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return objList;
	}

	private static List<Object[]> getResultList(ResultSet rs) throws SQLException
	{
		List<Object[]> objList = null;
		if (rs != null)
		{
			ResultSetMetaData rsmd = rs.getMetaData();
			Object objArr[] = null;
			int colCount = rsmd.getColumnCount();
			while (rs.next())
			{
				objArr = new Object[colCount];
				objList = (objList == null) ? (new ArrayList<Object[]>()) : objList;
				for (int ind = 0; ind < colCount; ind++)
				{
					objArr[ind] = rs.getObject(ind + 1);
				}
				objList.add(objArr);
			}
		}
		return objList;
	}

	private static List<Object> getResultList(ResultSet rs, Class cls)
	{
		List<Object> objList = null;
		String strObject = null;

		try
		{
			Method[] mthdArr = cls.getDeclaredMethods();
			Map<String, Method> objSettersMap = new HashMap<String, Method>();

			for (Method mthd : mthdArr)
			{
				if (mthd.getName().startsWith("set") && mthd.getParameterTypes().length > 0)
				{
					objSettersMap.put(mthd.getName(), mthd);
				}
			}

			if (rs != null)
			{
				ResultSetMetaData rsmd = rs.getMetaData();
				Object objArr[] = null;
				int colCount = rsmd.getColumnCount();
				mthdArr = new Method[colCount];

				for (int ind = 0; ind < colCount; ind++)
				{
					String fieldName = rsmd.getColumnLabel(ind + 1);
					String setterName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
					mthdArr[ind] = objSettersMap.get(setterName);
//					System.out.println(ind + " - " + fieldName + " - " + setterName);
				}
				objSettersMap.clear();
				while (rs.next())
				{
					objList = (objList == null) ? (new ArrayList<Object>()) : objList;
					Object pojoInstance = cls.newInstance();

					for (int ind = 0; ind < colCount; ind++)
					{
						Object obj = rs.getObject(ind + 1);
						if(obj!=null)
						{
							switch (obj.getClass().getSimpleName())
							{
							case "Integer":
								strObject = Integer.toString((int) obj);
								break;
							case "Float":
								strObject = Float.toString((float) obj);
								break;
							case "Double":
								strObject = Double.toString((double) obj);
								break;
							default:
								strObject = (String) obj;
							}
							
//							System.out.print(ind + " - " + mthdArr[ind].getName() + " - " );
//							System.out.print(mthdArr[ind].getParameterTypes() + " - ");
//							System.out.println(mthdArr[ind].getParameterTypes().length);
							if(strObject!=null && !strObject.trim().isEmpty())
							{
								switch (mthdArr[ind].getParameterTypes()[0].getSimpleName())
								{
								case "Integer":
									mthdArr[ind].invoke(pojoInstance, Integer.parseInt(strObject));
									break;
								case "Float":
									mthdArr[ind].invoke(pojoInstance, Float.parseFloat(strObject));
									break;
								case "Double":
									mthdArr[ind].invoke(pojoInstance, Double.parseDouble(strObject));
									break;
								default:
									mthdArr[ind].invoke(pojoInstance, strObject);
								}
							}
						}
					}
					objList.add(pojoInstance);
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return objList;
	}

	private static Object[] getSingleRow(ResultSet rs) throws SQLException
	{
		Object objArr[] = null;
		if (rs != null)
		{
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			while (rs.next())
			{
				objArr = new Object[colCount];
				for (int ind = 1; ind <= colCount; ind++)
				{
					objArr[ind] = rs.getObject(ind++);
				}
			}
		}
		return objArr;
	}

	private static Object getSingleCell(ResultSet rs) throws SQLException
	{
		Object obj = null;
		if (rs != null)
		{
			while (rs.next())
			{
				obj = rs.getObject(1);
			}
		}
		return obj;
	}

	public static void getSingleRow(Connection con, String qryString)
	{

	}

	public static void getSingleCell(Connection con, String qryString)
	{

	}

	public static void execute(Connection con, String qryString)
	{

	}

	private static void setResultToObj(Object objResult, String setterName, Object objInstance)
	{
		// objInstance.getClass().

	}
	
	  public static void dummyMethod()
	  {
/*	    // load the sqlite-JDBC driver using the current class loader
		  try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		  Connection connection = null;
	    try
	    {
	      // create a database connection
	      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
	      Statement statement = connection.createStatement();
	      statement.setQueryTimeout(30);  // set timeout to 30 sec.

	      statement.executeUpdate("drop table if exists person");
	      statement.executeUpdate("create table person (id integer, name string)");
	      statement.executeUpdate("insert into person values(1, 'leo')");
	      statement.executeUpdate("insert into person values(2, 'yui')");
	      ResultSet rs = statement.executeQuery("select * from person");
	      while(rs.next())
	      {
	        // read the result set
	        System.out.println("name = " + rs.getString("name"));
	        System.out.println("id = " + rs.getInt("id"));
	      }
	    }
	    catch(SQLException e)
	    {
	      // if the error message is "out of memory", 
	      // it probably means no database file is found
	      System.err.println(e.getMessage());
	    }
	    finally
	    {
	      try
	      {
	        if(connection != null)
	          connection.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
*/	  }
  }
